package vn.ugame.model.dal;

import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import vn.ugame.exception.DataConnectionFailed;
import vn.ugame.exception.InvalidEntityClass;
import vn.ugame.model.entity.Account;
import vn.ugame.model.entity.Customer;
import vn.ugame.model.entity.IntWapper;
import vn.ugame.model.entity.Order;

public class AccountDA {

    public Account findAccountById(String id)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Account account = null;
        if (id != null && !id.isEmpty()) {
            List<Account> accounts = findAccounts(id, null);
            if (accounts.size() > 0) {
                account = accounts.get(0);
            }
        }
        return account;
    }

    public Account findAccountByEmail(String email)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Account account = null;
        if (email != null && !email.isEmpty()) {
            List<Account> accounts = findAccounts(null, email);
            if (accounts.size() > 0) {
                account = accounts.get(0);
            }
        }
        return account;
    }

    public Customer findCustomerByEmail(String email)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Customer customer = null;
        if (email != null && !email.isEmpty()) {
            List<Customer> customers = findCustomers(null, null, email, null, null, null);
            if (customers.size() > 0) {
                customer = customers.get(0);
            }
        }
        return customer;
    }

    public Customer findCustomerByFullName(String fullName)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Customer customer = null;
        if (fullName != null && !fullName.isEmpty()) {
            List<Customer> customers = findCustomers(null, null, null, fullName, null, null);
            if (customers.size() > 0) {
                customer = customers.get(0);
            }
        }
        return customer;
    }

    public Customer findCustomerByPhoneNumber(String phoneNumber)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Customer customer = null;
        if (phoneNumber != null && !phoneNumber.isEmpty()) {
            List<Customer> customers = findCustomers(null, null, null, null, phoneNumber, null);
            if (customers.size() > 0) {
                customer = customers.get(0);
            }
        }
        return customer;
    }

    public List<Customer> findCustomers(String customerId, String accountId, String email,
            String fullName, String phoneNumber, Boolean newsletter)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        List<Customer> customerList = new ArrayList<Customer>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findCustomers(?,?,?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, customerId);
        cs.setString(2, accountId);
        cs.setString(3, email);
        cs.setString(4, fullName);
        cs.setString(5, phoneNumber);
        if (newsletter == null) {
            cs.setNull(6, Types.BIT);
        } else {
            cs.setBoolean(6, newsletter.booleanValue());
        }
        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Customer customer = factory.createEntity(rs, Customer.class);
            customerList.add(customer);
        }
        rs.close();
        cs.close();
        conn.close();
        return customerList;
    }

    public List<Account> findAccounts(String id, String email)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        List<Account> accountList = new ArrayList<Account>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findAccounts(?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, id);
        cs.setString(2, email);

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Account account = factory.createEntity(rs, Account.class);
            accountList.add(account);
        }
        rs.close();
        cs.close();
        conn.close();
        return accountList;
    }

    public void register(String email, String password, String fullName,
            String phoneNumber, boolean newsletter, String uCoin)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call register(?,?,?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, email);
        cs.setString(2, password);
        cs.setString(3, fullName);
        cs.setString(4, phoneNumber);
        cs.setBoolean(5, newsletter);
        cs.setString(6, uCoin);

        cs.execute();

        cs.close();
        conn.close();
    }

    public List<Order> findOrders(
            String customerName, String customerId, String phoneNumber,
            String itemTag, String itemId, Locale locale,
            String activatedCode, java.util.Date activatedDate, String imei,
            Double price, String priceUnit, Double priceVND,
            Integer fromIndex, Integer toIndex, IntWapper count)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        List<Order> orderList = new ArrayList<Order>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findOrders(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";

        CallableStatement cs = conn.prepareCall(query);
        if (customerName != null) {
            try {
                cs.setBytes(1, customerName.getBytes("UTF-8"));
            } catch (UnsupportedEncodingException ex) {
            }
        } else {
            cs.setNull(1, Types.VARCHAR);
        }
        cs.setString(2, customerId);
        cs.setString(3, phoneNumber);
        if (itemTag != null) {
            try {
                cs.setBytes(4, itemTag.getBytes("UTF-8"));
            } catch (UnsupportedEncodingException ex) {
            }
        } else {
            cs.setNull(4, Types.VARCHAR);
        }
        cs.setString(5, itemId);
        if (locale == null) {
            cs.setNull(6, Types.VARCHAR);
        } else {
            cs.setString(6, locale.getLanguage());
        }
        cs.setString(7, activatedCode);
        if (activatedDate == null) {
            cs.setNull(8, Types.DATE);
        } else {
            cs.setDate(8, new java.sql.Date(activatedDate.getTime()));
        }
        cs.setString(9, imei);
        if (price == null) {
            cs.setNull(10, Types.DOUBLE);
        } else {
            cs.setDouble(10, price.doubleValue());
        }
        cs.setString(11, priceUnit);
        if (priceVND == null) {
            cs.setNull(12, Types.DOUBLE);
        } else {
            cs.setDouble(12, priceVND.doubleValue());
        }
        if (fromIndex == null) {
            cs.setInt(13, 0);
        } else {
            cs.setInt(13, fromIndex.intValue());
        }
        if (toIndex == null) {
            cs.setInt(14, 0);
        } else {
            cs.setInt(14, toIndex.intValue());
        }
        cs.registerOutParameter(15, Types.INTEGER);

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Order o = factory.createEntity(rs, Order.class);
            orderList.add(o);
        }

        if (count != null) {
            count.setValue(cs.getInt(15));
        }

        rs.close();
        cs.close();
        conn.close();

        return orderList;
    }
}
